﻿
/*
'===============================================================================
'  MP.LayerHelper.MP_OBS.BL.HrPositionManager
'===============================================================================
*/

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using MP.LayerHelper.MP_OBS.BLL;
using MP.LayerHelper.MP_OBS.DAL;
using MP.LayerHelper.MP_OBS.DAL.EntityClasses;
using MP.LayerHelper.MP_OBS.DAL.FactoryClasses;
using MP.LayerHelper.MP_OBS.DAL.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;
using System.Collections;
using MP.Core.Library;
using System.Collections.Generic;

namespace MP.LayerHelper.MP_OBS.BLL
{
	public class HrPositionManager : HrPositionManagerBase
	{
		/// <summary>
		/// Purpose: Class constructor.
		/// </summary>
		public HrPositionManager()
		{
			// Nothing for now.
		}
		
		/// <summary>
		/// Method to create an instance of HrPositionManager
		/// </summary>
		/// <returns>An instant of HrPositionManager class</returns>
		public static HrPositionManager CreateInstant()
		{
			return new HrPositionManager();
		}
        /// <summary>
        /// 
        /// </summary>
        /// <param name="EmployeeId"></param>
        /// <param name="OrganizeId"></param>
        /// <returns></returns>
        public HrPositionEntity GetByEmployee_OrganizeId(Guid EmployeeId, Guid OrganizeId)
        {
            EntityCollection<HrPositionEntity> items = new EntityCollection<HrPositionEntity>();

            IPredicateExpression predicate = new PredicateExpression();
            predicate.Add(HrPositionFields.EmployeeId == EmployeeId);
            predicate.Add(HrPositionFields.OrganizeId == OrganizeId);
            predicate.Add(HrPositionFields.IsDeleted == false);

            RelationPredicateBucket filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(predicate);

            using (DataAccessAdapterBase adapter = new DataAccessAdapterFactory().CreateAdapter())
            {
                adapter.FetchEntityCollection(items, filter);
            }

            if (items != null && items.Count > 0)
                return items[0];
            return null;
        }
        /// <summary>
        /// Lấy danh sách nhân sự
        /// </summary>
        /// <param name="branchCode"></param>
        /// <param name="departCode"></param>
        /// <param name="jobCode"></param>
        /// <returns></returns>
        /// 
        public DataSet GetPosition(string branchCode, string departCode, string jobCode)
        {
            return GetPosition(branchCode, departCode, jobCode, "", "");
        }
        public DataSet GetPosition(string branchCode, string departCode, string jobCode, string groupCode, string users)
        {
            string strSQL = @"Select p.Id, b.BranhCode,d.DepartCode,j.JobCode,j.JobName,j.Priority,u.UserName,hr.FullName,b.Priority,d.Priority,g.AgentGroupName,hr.AgentNumber,hr.MachineId,g.Id AS GroupId
                                     ,b.Id BranchId,d.Id DepartId,j.Id JobId
                                     ,(Select count(*) From hrPosition p1 Where p1.UserId=p.UserId and p1.IsDeleted=0 and p1.IsMaster=0) as SubPosition
                              From ((((((aspnet_Users u INNER JOIN hrPosition p ON u.UserName=p.UserId)
                                   LEFT JOIN hrHumanOrganize o ON o.Id=p.OrganizeId)
                                   LEFT JOIN hrEmployee hr ON hr.WindowsUser=u.UserName)
                                   LEFT JOIN hrBranch b ON o.BranchId=b.Id) 
                                   LEFT JOIN maDepartment d ON o.DepartId=d.Id)
                                   LEFT JOIN hrJobTitle j ON o.JobId=j.Id)
                                   LEFT JOIN maAgentGroup g ON o.GroupId=g.Id
                              Where p.IsDeleted=0 and p.IsMaster=1 and  hr.IsDeleted=0 ";
            string tmpSQL = "";
            Hashtable param = new Hashtable();

            if (!string.IsNullOrEmpty(branchCode))
            {
                tmpSQL += " and b.BranhCode=@BranchCode ";
                param["BranchCode"] = branchCode;
            }

            if (!string.IsNullOrEmpty(departCode))
            {
                tmpSQL += " and d.DepartCode=@DepartCode ";
                param["DepartCode"] = departCode;
            }

            if (!string.IsNullOrEmpty(jobCode))
            {
                tmpSQL += " and j.JobCode=@JobCode ";
                param["JobCode"] = jobCode;
            }

            if (!string.IsNullOrEmpty(groupCode))
            {
                tmpSQL += " and g.AgentGroupCode=@AgentGroupCode ";
                param["AgentGroupCode"] = groupCode;
            }

            if (!string.IsNullOrEmpty(users))
            {
                tmpSQL += " and (hr.WindowsUser IN ({0}) OR hr.AgentNumber IN ({1}) ) ";
                users = users.Replace("'", "");
                tmpSQL = string.Format(tmpSQL, FString.SplitSearchString(users), FString.SplitSearchString(users));
            }

            if (!string.IsNullOrEmpty(tmpSQL))
                strSQL += "and " + tmpSQL.Substring(4) + " ";

            strSQL += "Order By b.Priority,BranhCode,d.Priority,DepartCode,j.Priority,JobName,u.UserName";
            return SqlHelper.ExecuteDataSet(SqlHelper.ConnectionStringOBS, param, CommandType.Text, strSQL);
        }

        public DataSet GetPositionSub(string username, string branchCode, string departCode, string jobCode)
        {
            string strSQL = @"Select p.Id, b.BranhCode,d.DepartCode,j.JobCode,j.JobName,j.Priority,u.UserName,hr.FullName,b.Priority,d.Priority
                                     ,b.Id BranchId,d.Id DepartId,j.Id JobId
                                     ,(Select count(*) From hrPosition p1 Where p1.UserId=p.UserId and p1.IsDeleted=0 and p1.IsMaster=0) as SubPosition
                              From (((((aspnet_Users u INNER JOIN hrPosition p ON u.UserName=p.UserId)
                                   LEFT JOIN hrHumanOrganize o ON o.Id=p.OrganizeId)
                                   LEFT JOIN hrEmployee hr ON hr.WindowsUser=u.UserName)
                                   LEFT JOIN hrBranch b ON o.BranchId=b.Id) 
                                   LEFT JOIN maDepartment d ON o.DepartId=d.Id)
                                   LEFT JOIN hrJobTitle j ON o.JobId=j.Id 
                              Where p.IsDeleted=0 and p.IsMaster=0 and p.UserId=@UserName ";
            string tmpSQL = "";
            Hashtable param = new Hashtable();
            param["UserName"] = username;

            if (!string.IsNullOrEmpty(branchCode))
            {
                tmpSQL += " and b.BranhCode=@BranchCode ";
                param["BranchCode"] = branchCode;
            }

            if (!string.IsNullOrEmpty(departCode))
            {
                tmpSQL += " and d.DepartCode=@DepartCode ";
                param["DepartCode"] = departCode;
            }

            if (!string.IsNullOrEmpty(jobCode))
            {
                tmpSQL += " and j.JobCode=@JobCode ";
                param["JobCode"] = jobCode;
            }

            //if (!string.IsNullOrEmpty(tmpSQL))
            //    strSQL += "Where " + tmpSQL.Substring(4) + " ";

            strSQL += "Order By b.Priority,BranhCode,d.Priority,DepartCode,j.Priority,JobName,u.UserName";
            return SqlHelper.ExecuteDataSet(SqlHelper.ConnectionStringOBS, param, CommandType.Text, strSQL);
        }

        /// <summary>
        /// Lấy user trong member chưa được phan bổ vị trí
        /// </summary>
        /// <returns></returns>
        public DataSet GetNewUser()
        {
            string strSQL = @"Select u.UserName, e.FullName
                              From (aspnet_Users u LEFT JOIN hrEmployee e ON e.WindowsUser=u.UserName)
                              Where u.UserName Not In (Select UserId From hrPosition Where IsDeleted=0) ";
            return SqlHelper.ExecuteDataSet(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL);
        }

        /// <summary>
        /// Cập nhật vị trí (thêm, sửa) nhân viên
        /// </summary>
        /// <param name="BranchId"></param>
        /// <param name="DepartId"></param>
        /// <param name="JobId"></param>
        /// <param name="UserId"></param>
        /// <param name="UserUpdate"></param>
        public void UpdatePosition(Guid BranchId, Guid DepartId, Guid JobId, string UserId, string UserUpdate)
        {
            string strSQL = "PRC_UpdatePosition";
            SqlParameter[] param = new SqlParameter[5];
            param[0] = new SqlParameter("@BranchId", BranchId);
            param[1] = new SqlParameter("@DepartId", DepartId);
            param[2] = new SqlParameter("@JobId", JobId);
            param[3] = new SqlParameter("@UserId", UserId);
            param[4] = new SqlParameter("@UserUpdate", UserUpdate);

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringOBS, CommandType.StoredProcedure, strSQL, param);
        }

        /// <summary>
        /// Cập nhật vi trí phụ
        /// </summary>
        /// <param name="BranchId"></param>
        /// <param name="DepartId"></param>
        /// <param name="JobId"></param>
        /// <param name="UserId"></param>
        /// <param name="UserUpdate"></param>
        public void UpdatePositionSub(Guid Id, Guid BranchId, Guid DepartId, Guid JobId, string UserId, string UserUpdate)
        {
            string strSQL = "PRC_UpdatePosition2";
            SqlParameter[] param = new SqlParameter[6];
            param[0] = new SqlParameter("@Id", Id);
            param[1] = new SqlParameter("@BranchId", BranchId);
            param[2] = new SqlParameter("@DepartId", DepartId);
            param[3] = new SqlParameter("@JobId", JobId);
            param[4] = new SqlParameter("@UserId", UserId);
            param[5] = new SqlParameter("@UserUpdate", UserUpdate);

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringOBS, CommandType.StoredProcedure, strSQL, param);
        }
        public HrPositionEntity GetByUserID(string UserID)
        {
            EntityCollection<HrPositionEntity> items = new EntityCollection<HrPositionEntity>();

            IPredicateExpression predicate = new PredicateExpression();
            predicate.Add(HrPositionFields.UserId == UserID);
            predicate.Add(HrPositionFields.IsDeleted == false);

            RelationPredicateBucket filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(predicate);

            using (DataAccessAdapterBase adapter = new DataAccessAdapterFactory().CreateAdapter())
            {
                adapter.FetchEntityCollection(items, filter);
            }

            if (items != null && items.Count > 0)
                return items[0];
            return null;
        }
        public HrPositionEntity GetByBranch_Depart_Job_User(Guid BranchId, Guid DepartId, Guid JobTitleId, string UserId)
        {
            EntityCollection<HrPositionEntity> items = new EntityCollection<HrPositionEntity>();

            IPredicateExpression predicate = new PredicateExpression();
            //predicate.Add(HrPositionFields.BranchId == BranchId);
            //predicate.Add(HrPositionFields.DepartId == DepartId);
            //predicate.Add(HrPositionFields.JobTitleId == JobTitleId);
            //predicate.Add(HrPositionFields.UserId == UserId);
            //predicate.Add(HrPositionFields.IsDeleted == false);

            RelationPredicateBucket filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(predicate);

            using (DataAccessAdapterBase adapter = new DataAccessAdapterFactory().CreateAdapter())
            {
                adapter.FetchEntityCollection(items, filter);
            }

            if (items != null && items.Count > 0)
                return items[0];
            return null;
        }
        public HrPositionEntity GetById(Guid Id)
        {
            EntityCollection<HrPositionEntity> items = SelectByIdLST(Id);
            if (items != null && items.Count > 0)
            {
                return (items[0]);
            }
            return null;
        }

        public HrPositionEntity GetByUserId(string userName)
        {
            EntityCollection<HrPositionEntity> items = SelectByUserIdLST(userName);
            if (items != null && items.Count > 0)
                return (items[0]);
            return null;
        }

        /// <summary>
        /// Lấy user cùng phòng ban với user hiện tại
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public DataSet GetUsersInDepart(string userName)
        {
            string strSQL = "PRC_GetUsersInDepart";
            SqlParameter param = new SqlParameter("@UserName", userName);
            return SqlHelper.ExecuteDataSet(SqlHelper.ConnectionStringOBS, CommandType.StoredProcedure, strSQL, param);
        }
       

        /// <summary>
        /// Lay danh sach chi nhanh co trong bang position
        /// </summary>
        /// <returns></returns>
        public DataTable GetBranch()
        {
            string strSQL = "HR_GetBranch2";
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.StoredProcedure, strSQL);
        }

        public DataTable GetDepart(Guid branchId)
        {
            string strSQL = "HR_Depart2";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@BranchId", branchId);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.StoredProcedure, strSQL, param);
        }

        public DataTable GetJob(Guid branchId, Guid departId)
        {
            string strSQL = "HR_GetJobTitle2";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@BranchId", branchId);
            param[1] = new SqlParameter("@DepartId", departId);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.StoredProcedure, strSQL, param);
        }

        public DataTable GetGroup(Guid branchId, Guid departId, Guid jobId)
        {
            string strSQL = "HR_Group2";
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("@BranchId", branchId);
            param[1] = new SqlParameter("@DepartId", departId);
            param[2] = new SqlParameter("@JobId", jobId);
            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.StoredProcedure, strSQL, param);
        }

        public List<HrHumanOrganizeEntity> GetOrganizeByEmployee(Guid employeeID)
        {
            List<HrHumanOrganizeEntity> lst = new List<HrHumanOrganizeEntity>();
            string sql = @" SELECT * 
                            FROM HrHumanOrganize ORG 
                            WHERE isDeleted = 0 
                                AND ORG.ID IN 
                                (SELECT OrganizeID
                                 FROM HrPosition
                                 WHERE EmployeeID = @EmployeeID
                            ";
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@employeeID",employeeID);
            DataTable tbl = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, sql, param);
            foreach(DataRow r in tbl.Rows)
            {
                HrHumanOrganizeEntity obj = new HrHumanOrganizeEntity();
                obj.Id = new Guid(r["id"].ToString());
                obj.GroupId = new Guid(r["GroupID"].ToString());
                obj.DepartId = new Guid(r["DepartID"].ToString());
                obj.BranchId = new Guid(r["BranchId"].ToString());
                obj.JobId = new Guid(r["JobId"].ToString());
                lst.Add(obj);
            }
            return lst;
        }

        public void UpdateUserId(string userold, string usernew)
        {
            string strSQL = "Update hrPosition Set UserId=@usernew Where UserId=@userold";
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@userold", userold);
            param[1] = new SqlParameter("@usernew", usernew);
            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringOBS, CommandType.Text, strSQL, param);
        }

//        /// <summary>
//        /// Lay danh sach cac phong ban cua user
//        /// </summary>
//        /// <param name="employeeID"></param>
//        /// <returns></returns>
//        public DataTable GetDepartmentByUser(Guid employeeID)
//        {
//            string sql = @" SELECT D.ID,D.DepartName,P.IsMaster 
//                            FROM MaDepartment AS D 
//                                INNER JOIN hrPosition P ON (P.IsDeleted=0 AND D.IsDeleted=0 AND P.DepartID = D.ID) 
//                                INNER JOIN hrEmployee E ON (P.IsDeleted=0 AND P.UserID = E.WindowsUser AND E.IsDeleted=0)
//                            WHERE E.ID=@employeeID 
//                            ORDER BY IsMaster DESC
//                        ";
//            SqlParameter[] param = new SqlParameter[1];
//            param[0] = new SqlParameter("@employeeID", employeeID);
//            return SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringOBS, CommandType.Text, sql, param);
//        }
      
	}
}
